﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Experts.Model;
using DBUtility;
using Experts.Framework.Mapping;

namespace Experts.DAL
{
    public class DALORM
    {
        public static int AddSaveBySql(object model)
        {
            //[1]获取实体对象的属性数组
            PropertyInfo[] proArray = model.GetType().GetProperties();
            //[2]获取标识列
            string identity = GetIdentity(proArray);
            //[3]获取所有非对应数据表字段的属性（扩展属性）
            List<string> nonPropertyList = GetNonFieldProperty(proArray);

            //组合SQL语句
            StringBuilder sqlFiled = new StringBuilder("insert into " + model.GetType().GetMappingName() + " (");
            StringBuilder sqlValue = new StringBuilder(" values (");

            foreach (PropertyInfo item in proArray)
            {
                //过滤标识列和非映射属性
                if (item.Name == identity) continue;
                if (nonPropertyList.Contains(item.Name)) continue;

                string columnValue = item.GetValue(model, null) + ""; //获取属性值
                if (columnValue == null) continue;  //如果属性值为null，我们一般不需要使用这个字段

                Type filedType = item.PropertyType;  //获取属性的类型
                if (filedType == typeof(DateTime))
                {
                    DateTime dt;
                    DateTime.TryParse(columnValue, out dt);  //将日期时间字符串，转换成标准的日期和时间格式
                    if (dt <= SqlDateTime.MinValue.Value)  //如果时间是最小值，则认为用户没有封装日期时间属性
                        continue;
                }

                sqlFiled.Append(item.Name + ",");  //在sql语句中添加一个字段名

                if (filedType == typeof(string) || filedType == typeof(DateTime))
                {
                    sqlValue.Append("'" + columnValue + "',");  //在sql语句中添加values的值，字符串类型必须添加单引号
                }
                else
                {
                    sqlValue.Append(columnValue + ",");
                }
            }
            //整合字段部分
            string start = sqlFiled.ToString();
            start = start.Substring(0, start.Length - 1) + ")";  //去掉最后一个逗号，添加半个括号
            //整合值部分
            string end = sqlValue.ToString();
            end = end.Substring(0, end.Length - 1) + ")";
            //完整的sql语句
            string sqlText = start + end;
            
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlText);
        }

        public static bool Update<T>(T t) where T : BaseModel
        {
            Type type = typeof(T);
            string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.Update);
            var paraList = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}",p.GetValue(t) ?? DBNull.Value)).ToArray();
            int iResult = SqlHelper.ExecuteNonQuery(CommandType.Text, sql, paraList);
            return iResult == 1;
        }

        public static List<T> Find<T>(int id) where T:BaseModel
        {
            string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.FindOne);
            //string sql = "select * from db_User where Id=@Id";
            SqlParameter para = new SqlParameter("@Id", id);
            return SqlHelper.GetList<T>(SqlHelper.ExcuteTable(sql, CommandType.Text, para));
        }
        public static int UpdateSaveBySql(object model)
        {
            //[1]获取实体对象的属性数组
            PropertyInfo[] proArray = model.GetType().GetProperties();
            //[2]获取标识列
            string identity = GetIdentity(proArray);
            //[3]获取所有非对应数据表字段的属性（扩展属性）
            List<string> nonPropertyList = GetNonFieldProperty(proArray);
            string identityValue = string.Empty;
            //组合SQL语句
            StringBuilder sqlFiled = new StringBuilder("update " + model.GetType().Name + " set ");
            //string columnValue = string.Join(",", proArray.Select(p => $"[{ p.Name}]='{p.GetValue(model)}'"));
            foreach (PropertyInfo item in proArray)
            {
                //过滤标识列和非映射属性
                if (item.Name == identity)
                {
                    identityValue = item.GetValue(model, null) + "";
                    continue;
                }
                if (nonPropertyList.Contains(item.Name)) continue;

                string columnValue = item.GetValue(model, null) + ""; //获取属性值
                if (columnValue == null) continue;  //如果属性值为null，我们一般不需要使用这个字段

                Type filedType = item.PropertyType;  //获取属性的类型
                if (filedType == typeof(DateTime))
                {
                    DateTime dt;
                    DateTime.TryParse(columnValue, out dt);  //将日期时间字符串，转换成标准的日期和时间格式
                    if (dt <= SqlDateTime.MinValue.Value)  //如果时间是最小值，则认为用户没有封装日期时间属性
                        continue;
                }

                sqlFiled.Append(item.Name + "='" + columnValue + "',");
            }
            //整合sql语句
            string strWhere = " where " + identity + "=" + identityValue;
            string sqlText = sqlFiled.ToString().TrimEnd(',') + strWhere;

            //调用普通的通用数据访问类
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlText);
        }
        /// <summary>
        /// 获取标识列属性
        /// </summary>
        /// <param name="proinfo"></param>
        /// <returns></returns>
        private static string GetIdentity(PropertyInfo[] proinfo)
        {
            string identity = string.Empty;
            foreach (PropertyInfo item in proinfo)
            {
                object[] attrs = item.GetCustomAttributes(false);
                foreach (var id in attrs)
                {
                    if (id.GetType().Name == "IdentityAttribute")
                    {
                        identity = item.Name;
                        break;
                    }
                }
            }
            return identity;
        }

        /// <summary>
        /// 获取非数据库字段属性
        /// </summary>
        /// <param name="proinfo"></param>
        /// <returns></returns>
        private static List<string> GetNonFieldProperty(PropertyInfo[] proinfo)
        {
            List<string> list = new List<string>();
            foreach (PropertyInfo item in proinfo)
            {
                object[] attrs = item.GetCustomAttributes(false);
                foreach (var id in attrs)
                {
                    if (id.GetType().Name == "NonTableAttribute")
                    {
                        list.Add(item.Name);
                        break;
                    }
                }
            }
            return list;
        }
    }
}
